package com.xty.itgroup.tech05tytex.Mapper;

import com.xty.itgroup.tech05tytex.Databean.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface Mapper01Base {
    @Delete("truncate table quote01yarn")
    public abstract void deleteYarns();

    @Insert("<script>insert into quote01yarn(yarn_id,standards,color,kind,component,enabled,del_change,create_time) " +
            "VALUES " +
            "<foreach collection='yarns' item='yarn' separator=','>" +
            "(" +
            "#{yarn.idcode},#{yarn.spec},#{yarn.color},#{yarn.kind},#{yarn.cf},#{yarn.del},#{yarn.idcode1},Now()" +
            ")" +
            "</foreach></script>")
    public abstract void addYarns(@Param("yarns") List<Map<String, Object>> yarns);

    @Delete("truncate table quote01yarn_new")
    public abstract void deleteYarnsNew();

    @Insert("<script>insert into quote01yarn_new(old_id,old_standards,old_color,old_component,yarn_name,yarn_price," +
            "yarn_id,yarn_tech) VALUES " +
            "<foreach collection='yarns' item='yarn' separator=','>" +
            "(" +
            "#{yarn.BaseId},#{yarn.GxSpec},#{yarn.GxColor},#{yarn.GxCf},#{yarn.ItemCode},#{yarn.Price},#{yarn" +
            ".IdCode},#{yarn.Gy}" +
            ")" +
            "</foreach></script>")
    public abstract void addYarnsNew(@Param("yarns") List<Map<String, Object>> yarns);

    @Delete("truncate table quote02bom_detail")
    public abstract void deleteGreyBom();

    @Insert("<script>insert into quote02bom_detail(weave_name,jw_type,weave_id,yarn_id,weight) VALUES " +
            "<foreach collection='boms' item='f' separator=','>" +
            "(" +
            "#{f.ItemCode},#{f.Jw},#{f.IdCode},#{f.Id},#{f.Kz}" +
            ")" +
            "</foreach></script>")
    public abstract void addGreyBom(@Param("boms") List<Map<String, Object>> boms);

    @Delete("truncate table quote02cloth_bom")
    public abstract void deleteGreyBomMain();

    @Insert("<script>insert into quote02cloth_bom(product_name,warp_sum,weft_mi,product_width,product_weight) VALUES " +
            "<foreach collection='boms' item='f' separator=','>" +
            "(" +
            "#{f.itemcode},#{f.jszj},#{f.sjwm},#{f.cpmf2},#{f.cpzl2}" +
            ")" +
            "</foreach></script>")
    public abstract void addGreyBomMain(@Param("boms") List<Map<String, Object>> boms);
    //-----------------添加 修改 标签------------------------------------------
    @Insert("insert into quote01tag(tag_name,tag_type,parent_id,tag_unit,enabled,create_time) values(#{tag_name}," +
            "#{tag_type},#{parent_id},#{tag_unit},0,Now())")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    public abstract void addTag(Bean01YarnTag tag);

    @Update("update quote01tag set enabled=1 where id=#{id} and enabled=0")
    public abstract void updateTag(int id);
    @Update("update quote01tag set parent_id=#{new_id} where parent_id=#{parent_id} and enabled=0")
    public abstract void updateTagByParentId(int parent_id,int new_id);


    @Select("SELECT id,tag_ids,yarn_id,yarn_price FROM `quote02yarn_tags` where (tag_ids like #{tag1} or tag_ids like" +
            " #{tag2} or tag_ids like #{tag3} or tag_ids=#{tag4}) and enabled=0 ")
    public abstract List<Bean02YarnTags> listYarnsByTag(String tag1, String tag2, String tag3, String tag4);

    //----------------- 绑定 解绑 换绑 纱线<->标签 -------------------------------------------------------
    //查重
    @Select("select id,tag_ids,yarn_id,yarn_price,create_time from quote02yarn_tags where enabled=0 and find_in_set" +
            "(yarn_id,#{yarn_ids})")
    public abstract List<Bean02YarnTags> listYarnTagsByIds(String yarn_ids);

    //获取所有标签
    @Select("SELECT t.id,t.tag_type,t.tag_name,t.parent_id,t.tag_unit,p.tag_name parent_name FROM quote01tag t LEFT JOIN quote01tag p on  p.enabled=0 and t.parent_id=p.id where t.enabled=0 ")
    public abstract List<Bean01YarnTag> listTags();

    //绑定
    @Insert("<script>insert into quote02yarn_tags(tag_ids,yarn_id,yarn_price,create_time,enabled) values " +
            "<foreach collection='tags' item='tag' separator=','>" +
            "(" +
            "#{tag.tag_ids},#{tag.yarn_id},#{tag.yarn_price},Now(),0" +
            ")" +
            "</foreach></script>")
    public abstract void addYarnTags(@Param("tags") List<Bean02YarnTags> tags);

    //解绑
    @Update("update quote02yarn_tags set enabled=1 where find_in_set(yarn_id,#{yarn_ids}) and enabled=0")
    public abstract void updateYarnTags(String yarn_ids);

    //根据主键获取
    @Select("select id,tag_ids,yarn_id,yarn_price,create_time from quote02yarn_tags where enabled=0 and id =#{id}")
    public abstract Bean02YarnTags getYarnTag(int id);

    //价格重新计算
    @Update("UPDATE quote02yarn_tags yt1,(SELECT yt.id,SUM(t.tag_unit) tag_unit FROM `quote02yarn_tags` yt,quote01tag" +
            " t where yt.enabled=0 and t.enabled=0 and  FIND_IN_SET(t.id,yt.tag_ids)  GROUP BY yt.id) a SET yt1" +
            ".yarn_price=a.tag_unit WHERE yt1.id=a.id")
    public abstract void calcualtUnit();

    //-----------------纱线 品名查询 -----------------------------------------------------------------------------------------
    @Select("<script>select yarn_name,yarn_price,yarn_id,yarn_tech from quote01yarn_new where yarn_name like " +
            "#{yarn_name}" +
            "<when test='type!=-1'> and yarn_id <when test='type!=1'> not </when> in (SELECT yarn_id FROM " +
            "quote02yarn_tags) </when>" +
            "</script>")
    public abstract List<Bean01YarnNew> listYarns(String yarn_name, int type);

    //----------------- 生产环节绑定  --------------------------------------------------------------------------------------
    @Select("SELECT id,tag_ids,weave_name,weave_loss,weave_cost,rate,grey_cost,weft_mi ,profit, cloth_price, " +
            "final_price FROM `quote02yarn_tags` where (tag_ids like #{tag1} or tag_ids " +
            "like #{tag2} or tag_ids like #{tag3} or tag_ids=#{tag4}) and enabled=0 ")
    public abstract List<Bean02ClothTags> listClothByTag(String tag1, String tag2, String tag3, String tag4);

    @Update("update quote02cloth_tags set enabled=1 where find_in_set(weave_name,#{names}) and enabled=0")
    public abstract void updateClothTags(String names);

    @Insert("<script>insert into quote02cloth_tags(tag_ids,weave_name,weave_loss,weave_cost,rate,grey_cost,weft_mi," +
            "profit, cloth_price, final_price, create_time,enabled) " +
            "values " +
            "<foreach collection='tags' item='tag' separator=','>" +
            "(" +
            "#{tag.tag_ids},#{tag.weave_name},#{tag.weave_loss},#{tag.weave_cost},#{tag.rate},#{tag.grey_cost},#{tag" +
            ".weft_mi},#{tag.profit}, #{tag.cloth_price}, #{tag.final_price}," +
            "Now(),0" +
            ")" +
            "</foreach></script>")
    public abstract void addClothTags(@Param("tags") List<Bean02ClothTags> tags);
    @Insert("insert into quote02cloth_tags(tag_ids,weave_name,weave_loss,weave_cost,rate,grey_cost,weft_mi," +
            "profit, cloth_price, final_price,kg_price, create_time,enabled,remark,union_id,user_name) " +
            "values  (" +
            "#{tag_ids},#{weave_name},#{weave_loss},#{weave_cost},#{rate},#{grey_cost},#{weft_mi},#{profit}, " +
            "#{cloth_price}, #{final_price},#{kg_price},Now(),0,#{remark},#{union_id},#{user_name})"
          )
    public abstract void addClothTag(Bean02ClothTags tag);
    //查询产品
    @Select("SELECT param_id,product_name,warp_sum,weft_mi,product_weight,product_width FROM `quote02cloth_bom` where" +
            " product_name like #{product_name}")
    public abstract List<Bean02ClothBom> listProduct(String product_name);

    @Select("SELECT param_id,product_name,warp_sum,weft_mi,product_weight,product_width FROM `quote02cloth_bom` where" +
            " product_name = #{product_name}")
    public abstract Bean02ClothBom getProduct(String product_name);

    @Select("SELECT id,tag_ids,weave_name,weave_loss,weave_cost,rate,grey_cost,weft_mi ,profit, cloth_price," +
            "final_price,kg_price,remark,union_id,user_name FROM `quote02cloth_tags` where weave_name = " +
            "#{product_name} " +
            "and enabled=0")
    public abstract Bean02ClothTags getClothByName(String product_name);
    @Update("update quote02cloth_tags set enabled=1 where id =#{id}")
    public abstract void deleteClothTags(int id);

    @Select("SELECT y.yarn_name,y.yarn_tech,d.weave_id,d.weave_name,d.jw_type,d.yarn_id,d.weight,t.tag_ids,t" +
            ".yarn_price,t.`names` FROM quote01yarn_new y,quote02bom_detail d LEFT JOIN (SELECT yt.*,GROUP_CONCAT(t" +
            ".tag_name) `names` FROM quote01tag t,quote02yarn_tags  yt where FIND_IN_SET(t.id,yt.tag_ids) and t" +
            ".enabled=0 and yt.enabled=0 GROUP BY yt.yarn_id) t on d.yarn_id=t.yarn_id and t.enabled=0 where " +
            "weave_name = #{product_name} and d.yarn_id=y.yarn_id")
    public abstract List<Map<String, Object>> listBomDetail(String product_name);

    //带价格纱线列表
    @Select("SELECT yt.id,yt.tag_ids,yt.yarn_id,yt.yarn_price,y.yarn_name,y.yarn_tech,GROUP_CONCAT(t.tag_name) " +
            "`names` FROM quote02yarn_tags yt,quote01yarn_new y,quote01tag t where yt.yarn_id=y.yarn_id and yt" +
            ".enabled=0 and FIND_IN_SET(t.id,yt.tag_ids) and y.yarn_name like #{yarn_name} GROUP BY yt.yarn_id")
    public abstract List<Map<String, Object>> listYarnsWithUnit(String yarn_name);

    //带价格布的列表(模糊查询)
    @Select("SELECT ct.id,ct.tag_ids,ct.weave_name,ct.weave_loss,ct.weave_cost,ct.rate,ct.grey_cost, ct.profit, ct " +
            ".cloth_price,ct.final_price,ct.final_price5,ct.final_price10,ct.final_price15,ct.final_price20,ct.kg_price,c.warp_sum,c.weft_mi,c.product_weight,c.product_width,IF" +
            "(GROUP_CONCAT(t" +
            ".tag_name) is null,null,GROUP_CONCAT(t.tag_name) ) as `names`,DATE_FORMAT(ct.create_time, '%Y-%m-%d') " +
            "create_time,ct.remark,ct.union_id,ct.user_name FROM quote02cloth_bom c, quote02cloth_tags" +
            " ct , quote01tag t   where 1=1 and " +
            " IF(#{flag}=-1,1=1,ct.enabled=#{flag})" +
            "and c.product_name=ct.weave_name and ct.weave_name like #{product_name} and  FIND_IN_SET(t" +
            ".id,ct.tag_ids) group by ct.id order by ct.create_time desc"
    )
    public abstract List<Map<String, Object>> listClothWithUnit(String product_name , int flag);
    //带价格布的列表(精确查询)
    @Select("SELECT ct.id,ct.tag_ids,ct.weave_name,CONCAT_WS('/',ct.weave_loss,ct.weave_cost,ct.rate,ct.grey_cost,ct" +
            ".profit) as params ,ct.cloth_price,ct.final_price,ct.kg_price,c.warp_sum,c.weft_mi,c.product_weight,c" +
            ".product_width," +
            "GROUP_CONCAT(t.tag_name) as `names`,DATE_FORMAT(ct.create_time, '%Y-%m-%d') create_time,ct.remark,ct" +
            ".union_id,ct.user_name FROM " +
            "quote02cloth_bom c," +
            "quote01tag t, quote02cloth_tags ct where 1=1 and " +
            " IF(#{flag}=-1,1=1,ct.enabled=#{flag})" +
            " and c.product_name=ct.weave_name and ct.weave_name = #{product_name} and  FIND_IN_SET(t.id,ct" +
            ".tag_ids) group by ct.id order by ct.create_time ")
    public abstract List<Map<String, Object>> listClothWithUnit1(String product_name,int flag);


}
